package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT\n" +
            "b2.`name`,o.final_total_amount value\n" +
            "FROM order_info o \n" +
            "INNER JOIN user_info i ON o.user_id=i.id \n" +
            "INNER JOIN order_detail d  ON o.id=d.order_id \n" +
            "INNER JOIN sku_info s ON d.sku_id=s.id \n" +
            "INNER JOIN base_category3 b3 ON s.category3_id=b3.id \n" +
            "INNER JOIN base_category2 b2 ON b3.category2_id=b2.id\n" +
            "GROUP BY b2.`name`")
    List<Sugar> get1();

    @Select("SELECT  \n" +
            "CONCAT(SUBSTR(i.birthday,3,1),'0后') name,\n" +
            "o.final_total_amount value\n" +
            "FROM order_info o \n" +
            "INNER JOIN user_info i ON o.user_id=i.id \n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN sku_info s ON d.sku_id=s.id \n" +
            "INNER JOIN base_trademark t ON s.tm_id=t.id\n" +
            "GROUP BY name")
    List<Sugar> get2();

    @Select("SELECT yys name,sum(final_total_amount) value FROM order_info WHERE yys is not null GROUP BY yys")
    List<Sugar> get3();

    @Select("SELECT  \n" +
            "t.`name`,\n" +
            "if(i.gender='F','男','女') sex,\n" +
            "o.final_total_amount sum\n" +
            "FROM order_info o \n" +
            "INNER JOIN user_info i ON o.user_id=i.id \n" +
            "INNER JOIN order_detail d  ON o.id=d.order_id \n" +
            "INNER JOIN sku_info s ON d.sku_id=s.id \n" +
            "INNER JOIN base_trademark t ON s.tm_id=t.id\n" +
            "GROUP BY t.`name`,sex")
    List<Sugar1> get4();
}
